|
In database relational modeling and implementation, a unique key is a set of zero, one, or more attributes. The value(s) of these attributes are required to be unique for each tuple (row) in a relation. The value, or combination of values, of unique key attributes for any tuple should not be repeated for any other tuple in that relation. When more than one column is combined to form a unique key, their combined value is used to access each row and maintain uniqueness. These keys are referred to as aggregate. or compound keys. Values are not combined, they are compared using their data types. When a column or set of columns is defined as unique to the database management system, the system verifies that each set of value(s) is unique before assigning the constraint. After the column(s) are defined as unique, an error will occur if an insertion is attempted with values that already exist. Some systems do not allow key values to be updated, and all systems will not allow duplicates. This ensures that uniqueness is maintained in both the primary table and any relations that are later bound to it. ==Summary== In an entity-relationship diagram of a data model, one or more unique keys may be declared for each data entity. Each unique key is composed from one or more data attributes of that data entity. The set of unique keys declared for a data entity is often referred to as the candidate keys for that data entity. From the set of candidate keys, a single unique key is selected and declared the ''primary key'' for that data entity. In an entity relationship diagram, each entity relationship uses a unique key, most often the primary key, of one data entity and copies the unique key data attributes to another data entity to which it relates. This inheritance of the unique key data attributes is referred to as a foreign key and is used to provide data access paths between data entities. Once the data model is instantiated into a database, each data entity usually becomes a database table, unique keys become unique indexes associated with their assigned database tables, and entity relationships become foreign key constraints. In integrated data models,〔(Data Model Integration | The Integration of Data Models )〕 commonality relationships〔(Commonality Relationships | Commonality Constraints )〕 do not become foreign key constraints since commonality relationships are a peer-to-peer type of relationship. The primary key may consist of a single attribute or a multiple attributes in combination. For example, a birthday could be shared by many people and so would not be a prime candidate for the primary key, but a Social Security Number or driver's license number would be ideal since it correlates to one single data value. There are several types of unique keys used in database modeling and implementations.〔(【引用サイトリンク】url=http://www.agiledata.org/essays/keys.html )〕 At the most basic definition, "a key is a unique identifier", so ''unique'' key is redundant. Keys that are within their originating entity are unique within that entity. Keys that migrate to another entity may or may not be unique, depending on the design and how they are used in the other table. Foreign keys may be the primary key in another table; for example a PersonID may become the EmployeeID in the Employee table. In this case, the EmployeeID is both a foreign key and the unique primary key, meaning that the tables have a 1:1 relationship. In the case where the person entity contained the biological father ID, the father ID would not be expected to be unique because a father may have more than one child. Here is an example of a primary key becoming a foreign key on a related table. ID migrates from the Author table to the Book table. Author Table Schema: Author(ID, Name, Address, Born) Book Table Schema: Book(ISBN, AuthorID, Title, Publisher, Price) Here ID serves as the primary key in the table 'Author', but also as AuthorID serves as a Foreign Key in the table 'Book'. The Foreign Key serves as the link, and therefore the connection, between the two related tables in this sample database. In a relational database, a candidate key uniquely identifies each row of data values in a database table. A candidate key comprises a single column or a set of columns in a single database table. No two distinct rows or data records in a database table can have the same data value (or combination of data values) in those candidate key columns since NULL values are not used. Depending on its design, a database table may have many candidate keys but at most one candidate key may be distinguished as the primary key. A key constraint applies to the set of tuples in a table at any given point in time. A key is not necessarily a unique identifier across the population of all ''possible'' instances of tuples that could be stored in a table but it does imply a data integrity rule that duplicates should not be allowed in the database table. Some possible examples of unique keys are Social Security Numbers, ISBNs, vehicle registration numbers or user login names. The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the SQL standard mainly as a convenience to the application programmer. Unique keys as well as primary keys may be logically referenced by foreign keys, but most RDBMS only allow a foreign key constraint against a primary key. 抄文引用元・出典: フリー百科事典『 ウィキペディア（Wikipedia）』 ■ウィキペディアで「Unique key」の詳細全文を読む スポンサード リンク
|